namespace CISLib
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class ManageCity
    {
        public static SqlDataReader GetCity()
        {
            string strSQL = "Select * from TB_Region  where ParentId <> '00000000-0000-0000-0000-000000000000'order by ParentId ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetCity(Guid areaId)
        {
            string strSQL = "Select * from TB_Region  where areaId = @AreaId and ParentId <> '00000000-0000-0000-0000-000000000000'order by ParentId ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetCityViaProvinceId(Guid id)
        {
            string strSQL = "Select * from TB_Region  where ParentId <> '00000000-0000-0000-0000-000000000000'and ParentId = @ParentId order by ParentId ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "ParentId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static void InsertNewCity(string name, string scName, Guid parentId)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "RegionName", SqlDbType.VarChar);
            MyDB.AddParameters(scName, "RegionSCName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(parentId, "ParentId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into Tb_Region (RegionName,RegionSCName,RegionId,ParentId) values (@RegionName,@RegionSCName,@RegionId,@ParentId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewCity(string name, string scName, Guid parentId, Guid areaId)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "RegionName", SqlDbType.VarChar);
            MyDB.AddParameters(scName, "RegionSCName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "RegionId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(parentId, "ParentId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into Tb_Region (RegionName,RegionSCName,RegionId,ParentId,AreaId) values (@RegionName,@RegionSCName,@RegionId,@ParentId,@AreaId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static bool IsExistSameCityName(string name, Guid areaId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "RegionName", SqlDbType.VarChar);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from tb_Region where RegionName = @RegionName and AreaId=@AreaId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveCityViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "RegionId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From tb_Region where RegionId = @RegionId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCity(string name, string scName, Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "RegionName", SqlDbType.VarChar);
            MyDB.AddParameters(scName, "RegionSCName", SqlDbType.VarChar);
            MyDB.AddParameters(id, "RegionId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update tb_Region set RegionName = @RegionName,RegionSCName = @RegionSCName where RegionId = @RegionId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

